<html>
<head>
<style type="text/css">

table{	
border-collapse:collapse;
table-layout:fixed;	
}
td.info
{
padding-right:10px;
}
tr.tables
{
border:1px solid darkgrey;
background-color:black;
color:white;
}

tr.attributes
{
border:1px solid white;
background-color:#303030;
color:white;
}
tr.rowa
{
border:1px solid white;
background-color:#D0D0D0;
color:black;
}
tr.rowb
{
border:1px solid white;
background-color:white;
color:black;
}
tr.rowc
{
border:1px solid darkgrey;
background-color:darkgrey;
color:black;
}
</style>
</head>
<body>

<?php
$id=$_GET["id"]; // SANITIZE THIS


		
print "<div align=center><form action=search.php method=post>";
print "<input type=text maxlength=100 name=query size='150' value='". htmlspecialchars($raw_query)."'></input>";
print "<input type=submit  value=Run></input></form><br>";


//SOuRCE FOR LINKS HERE
print "<div align=center><table width=800>
		<tr class=rowb>
		<td><a href=addmovie.php><b>Add Movie</b></a></td>
		<td><a href=addmovieactor.php><b>Add Movie Actor</b></a></td>
		<td><a href=addmoviedirector.php><b>Add Movie Director</b></a></td>
		<td><a href=add_actor_director.php><b>Add Actor or Director</b></a>	</td>
		</tr>
		</table></div>";
		
function connect_to_server(){
	$DB="CS143";
	$db_connection = mysql_connect("localhost", "cs143", "");
	if(!$db_connection) {
		$errmsg = mysql_error($db_connection);
		print "Connection failed: " .$errmsg ."<br />";
		exit(1);
	}
	mysql_select_db("$DB", $db_connection);
	return $db_connection;

}

function make_query($command, $db_connection){
		
	$resTable = mysql_query($command, $db_connection);
	if(!$resTable) {
		$errmsg = mysql_error($db_connection);

		if($command != ""){
			print "Query fetch failed: " .$errmsg ."<br />";
		}

		exit(1);
	
	}
	return $resTable;
	
}

$conn=connect_to_server();



if(isset($_POST["AddComment"])){
		$comment["name"]["value"]=mysql_real_escape_string($_POST["name"], $conn);
		$comment["rating"]["value"]=$_POST["rating"];
		$comment["comment"]["value"]=mysql_real_escape_string($_POST["comment"], $conn);
		$comment["time"]["value"] =date( 'Y-m-d H:i:s',  time());;
		$id = $_POST["id"];
		
		$query = "INSERT INTO Review (mid,name,rating,comment,time) VALUES(".
				 $id . ",\"".
				 $comment["name"]["value"]."\",".$comment["rating"]["value"].",\"".
				 $comment["comment"]["value"] . "\",\"".$comment["time"]["value"] . "\");";
		make_query($query,$conn);		 
		
} 

$query = "SELECT title, year,rating,company  FROM Movie WHERE id=%d;";
if(!preg_match("/^[0-9]+$/", $id)){
		print "<h1>Invalid Id</h1>
			   <img src='data/error.png'/><br>
			   <b>The id \"$id\" is not an acceptable id.</b>
			   ";
		exit();
}
$query_filled = sprintf($query, $id);
$res=make_query($query_filled, $conn);
$row = mysql_fetch_row($res);
if($row== null){
		print "<h1>Movie Not Found</h1>
			   <img src='data/filenotfound.png'/><br>
			   <b>The Movie with the id $id does not exist! please select another movie.</b>";
	
	
		exit();
}
$data["title"] = $row[0];
$data["year"] = $row[1];
$data["rating"] = $row[2];
$data["company"] = $row[3];

$query = "SELECT AVG(rating) FROM Review WHERE mid=%d;";
$query_filled = sprintf($query, $id);
$res = make_query($query_filled, $conn);
$row = mysql_fetch_row($res);
if($row != null)
	$data["avgRating"] =  $row[0] ;
else
	$data["avgRating"] = null;

$query = "SELECT genre FROM MovieGenre WHERE mid=%d;";
$query_filled = sprintf($query, $id);
$res=make_query($query_filled, $conn);

$data["genres"] =array();
$i=0;
while($row = mysql_fetch_row($res)){
	$data["genres"][$i] =  $row[0] ;
	$i++;
}

$data["genre"] ="";
while($row = mysql_fetch_row($res)){
	$data["genre"] = $data["genre"] . $row[0] . ",";
}

$query = "SELECT first,last, id  FROM MovieDirector,Director WHERE mid=%d AND did=id;";
$query_filled = sprintf($query, $id);
$res=make_query($query_filled, $conn);

$data["directors"] =array();
$i=0;
while($row = mysql_fetch_row($res)){
	$data["directors"][$i]["name"] =  $row[0] . " " . $row[1] ;
	$data["directors"][$i]["id"] =  $row[2] ;
	$i++;
}


$query = "SELECT first,last,id, role FROM MovieActor, Actor WHERE MovieActor.mid=%d AND MovieActor.aid=Actor.id;";
$query_filled = sprintf($query, $id);
$res=make_query($query_filled, $conn);
$i=0;
$data["actors"] =array();
while($row = mysql_fetch_row($res)){
	$data["actors"][$i]["name"] =$row[0] . " " . $row[1] ;
	$data["actors"][$i]["role"] =$row[3] ;
	$data["actors"][$i]["id"] =$row[2];
	$i++;
}

$query = "SELECT name,comment,rating,time FROM Review WHERE Review.mid=%d;";
$query_filled = sprintf($query, $id);
$res=make_query($query_filled, $conn);
$i=0;
$data["reviews"] =array();
while($row = mysql_fetch_row($res)){
	$data["reviews"][$i]["name"] =$row[0];
	$data["reviews"][$i]["comment"] =$row[1] ;
	$data["reviews"][$i]["rating"] =$row[2];
	$data["reviews"][$i]["time"] =$row[3] ;
	$i++;
}

$directorphp="director.php";
$genrephp="genre.php";
$actorphp="actor.php";

print "
<table>

<tr><h1>$data[title] ($data[year])</h1></tr>

<tr><td><img src='data/movieimage.png'/></td><td>
<table>

<tr class=rowa>
<td class = info>Genres</td>
<td class = info>";
for($i =0; $i < sizeof($data["genres"]); $i++){
	print "<a href=$genrephp?id=".$data[genres][$i].">".$data[genres][$i]."</a>    ";
}

print "</td>
</tr>

<tr class=rowb>
<td class = info>Directors</td>
<td>";
for($i =0; $i < sizeof($data["directors"]); $i++){
	print "<a href=$directorphp?id=".$data[directors][$i][id].">".$data[directors][$i][name]."</a>   ";
}
print "<a href=addmoviedirector.php?mid=".$id.">(Add Director)</a>";
print "</td>
</tr>

<tr class=rowa>
<td class = info>Year</td>
<td>$data[year]</td>
</tr>

<tr class=rowb>
<td class = info>Company</td>
<td>$data[company]</td>
</tr>

<tr class=rowa>
<td class = info>Rating</td>
<td>$data[rating]</td>
</tr>

<tr class=rowb>
<td class = info>Rank</td>";
if($data[avgRating] != null){
	print "<td>$data[avgRating]</td>";
}
else
	print "<td>No Reviews</td>";
print "
</tr>


</table>
</td></tr>
</table>
";

$elipses = "...";

print "<h2>Actors</h2>
	<table width=1000px><tr><td width=\"200\"></td>
	<td width=\"30\"></td><td width=\"150\"></td></tr>";
for($i=0; $i < sizeof($data["actors"]); $i++){
	
	if($i%2==0)
		$type="rowa";
	else
		$type="rowb";
		
	print "<tr class=$type><td>
			<a href=$actorphp?id=".$data[actors][$i][id].">".$data[actors][$i][name]."</a>
			</td><td>$elipses</td><td>".$data[actors][$i][role]."</td></tr>";	
}
print "<tr><td></td><td></td><td><a href=addmovieactor.php?mid=".$id.">Add Roles</a></td></tr>";
print "</table><br><br>
		<h2>Add a Comment</h2>
		
		<form action=movie.php method=post>
		<input maxlength=20 type=text name=name value=\"Anonymous\"/>
		Rating <select name=rating>
		<option value=0>0</option>
		<option value=1>1</option>
		<option value=2>2</option>
		<option value=3>3</option>
		<option value=4>4</option>
		<option value=5>5</option>
		</select>
		<br>
		<textarea  maxlength=500 name=comment value=Comment rows=5 cols=100></textarea><br>
		<input type=hidden readonly=\"readonly\" name=id value=\"".$id."\"/>
		<input type=submit  name=AddComment value='Add Comment'></input>
		</form>";
		
		
print "<h3>Comments</h3>";
print "<table border=1  width=1000px>";	
for($i=0; $i < sizeof($data[reviews]); $i++){
	print "<tr class=rowc>
			<td><b>Name</b></td>
			<td><b>Date</b></td>
			<td><b>Rating</b></td>
			</tr>";
			
	print "<tr class=rowa>
			<td><b>".$data["reviews"][$i]["name"]."</b> </td>  ";
	print "<td><i>".$data["reviews"][$i]["time"]."</i>  </td> ";
	print "<td>".$data["reviews"][$i]["rating"]."<b></td></tr>";
	print "<tr><td colspan=3>". $data["reviews"][$i]["comment"] . "</td></tr>";
}
print "</table></div>";


mysql_close($conn);

?>

<br>
</body>
</html>
